package com.xinchuang.service.impl;

import com.alibaba.fastjson.JSON;
import com.xinchuang.comment.KeyWord;
import com.xinchuang.comment.R;
import com.xinchuang.domain.pojo.TryOutDeviceMod;
import com.xinchuang.domain.vo.TempVo;
import com.xinchuang.domain.vo.df.*;
import com.xinchuang.service.ExportDfOutQuotationService;
import com.xinchuang.service.IMoldSelectionService;
import com.xinchuang.service.actual.TryOutDeviceModService;
import com.xinchuang.utils.BigdecimalUtil;
import com.xinchuang.utils.DateUtils;
import com.xinchuang.utils.PoiUtils;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.Lists;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.IntStream;


/**
 * @Description:导出东风对外报价
 * @Author: yufeng
 * @Date: 2023/10/15 11:24
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class ExportDfOutQuotationServiceImpl implements ExportDfOutQuotationService {
    private final KeyWord keyWord;

    @Autowired
    private TryOutDeviceModService tryOutDeviceModService;

    @Autowired
    private IMoldSelectionService iMoldSelectionService;

    @Override
    public String exportNormalOutQuotation(String data, HttpServletResponse res) {
        log.info("东风对外报价单导出start");
        R r = JSON.parseObject(data, R.class);
        DfQuotationVo normalOutQuotationResVo = JSON.parseObject(JSON.toJSONString(r.getData()), DfQuotationVo.class);
        String moldSelectionId = normalOutQuotationResVo.getMoldSelectionId();
        String projectName = iMoldSelectionService.getProjectName(moldSelectionId);
        // 文件名称
        String fileName = projectName + "项目-东风对外报价单-" +  DateUtils.dateTimeNow("yyyyMMdd-SSS");
        //返回地址
        String resultPath = "file" + File.separator + fileName + ".xlsx";
        String filePath = keyWord.getFileUrl();
        String fullPath = "";
        String os = System.getProperty("os.name");
        if (os.contains("Linux")) {
            log.info("Linux 系统");
            fullPath = filePath + File.separator + resultPath;
        } else {
            log.info("其他系统");
            fullPath = filePath + File.separator + resultPath;
        }
        XSSFWorkbook workbook = new XSSFWorkbook();
        try {
            createExcelFile(workbook, normalOutQuotationResVo, fullPath, fileName, filePath);
        } catch (Exception e) {
            log.error("东风对外报价单失败", e.getMessage());
            throw new RuntimeException(e);
        }
        log.info("东风对外报价单导出end");
        return resultPath;
    }

    public File createExcelFile(XSSFWorkbook workbook, DfQuotationVo vo, String fullPath, String fileName, String filePath) {
        XSSFSheet sheet = workbook.createSheet("总表");
        sheet.setZoom(55); // 设置默认缩放比例为100%

        // 设置多列的列宽
        sheet.setColumnWidth(0, 15 * 256);
        sheet.setColumnWidth(1, 19 * 256);
        sheet.setColumnWidth(2, 19 * 256);
        sheet.setColumnWidth(3, 25 * 256);
        sheet.setColumnWidth(4, 15 * 256);
        sheet.setColumnWidth(5, 17 * 256);
        sheet.setColumnWidth(6, 21 * 256);
        sheet.setColumnWidth(7, 11 * 256);
        sheet.setColumnWidth(8, 14 * 256);
        sheet.setColumnWidth(9, 22 * 256);
        sheet.setColumnWidth(10, 14 * 256);
        sheet.setColumnWidth(11, 13 * 256);
        sheet.setColumnWidth(12, 12 * 256);
        sheet.setColumnWidth(13, 21 * 256);
        sheet.setColumnWidth(14, 14 * 256);
        sheet.setColumnWidth(15, 16 * 256);
        sheet.setColumnWidth(16, 20 * 256);
        sheet.setColumnWidth(17, 14 * 256);
        sheet.setColumnWidth(18, 18 * 256);
        sheet.setColumnWidth(19, 23 * 256);
        sheet.setColumnWidth(20, 19 * 256);
        sheet.setColumnWidth(21, 23 * 256);
        sheet.setColumnWidth(22, 10 * 256);
        sheet.setColumnWidth(23, 9 * 256);

        mergedRegionNoStyle(sheet, 0, 0, 0, 23, "成都航天模塑股份有限公司成都模具分公司报价单");


        // 设置地址、Tel、E-mail行
        String[] str1 = {"地址:", "Tel:", "E-mail:"};
        String[] str2 = {vo.getAddress(), vo.getTEL(), vo.getEMail()};
        String[] str3 = {"邮编:" + vo.getZIP(), "Fax:" + vo.getFAX(), "网址:" + vo.getWebsite()};
        for (int i = 1; i < 4; i++) {
            getRow(sheet, i).createCell(0).setCellValue(str1[i - 1]);
            getRow(sheet, i).createCell(1).setCellValue(str2[i - 1]);
            getRow(sheet, i).createCell(21).setCellValue(str3[i - 1]);
        }
        String[] str5 = {"TO", "FROM", "联系人", "联系方式"};
        String[] str6 = {vo.getTo(), vo.getFrom(), vo.getContactName(), vo.getContactPhone()};
        for (int i = 4; i < 8; i++) {
            getRow(sheet, i).createCell(0).setCellValue(str5[i - 4]);
            getRow(sheet, i).createCell(1).setCellValue(str6[i - 4]);
        }
        // 设置模具报价单标题行
        mergedRegionNoStyle(sheet, 8, 8, 0, 23, "模具报价单");
        NumberFormat formatter2 = new DecimalFormat("#0.00");
        // 设置项目名称行
        String projectName = vo.getProjectName();
        mergedRegionNoStyle(sheet, 9, 9, 0, 3, "项目名称： " + projectName);
        if (null != vo.getUnit()) {
            getRow(sheet, 9).createCell(21).setCellValue("单位:" + vo.getUnit());
        } else {
            getRow(sheet, 9).createCell(21).setCellValue("单位:");
        }

        getRow(sheet, 9).getCell(0).setCellStyle(customCellStyle(workbook, 16, false, false, "left"));
        getRow(sheet, 9).getCell(21).setCellStyle(customCellStyle(workbook, 16, false, false, "left"));

        // 设置数据title行
        String[] str11 = {"序号", "零件号", "零件名称", "图片", "表面处理", "产品材料", "产品尺寸", "型腔数", "产品净重（kg）", "入水形式", "前模钢材", "后模钢材", "模架钢材", "运动件（斜顶\\直顶\\滑块）", "油缸", "吊环", "模具尺寸", "注塑设备（T)", "皮纹费用", "模具费用（万）", "模具总价（万）", "制作周期", "寿命", "备注"};
        for (int i = 0; i < str11.length; i++) {
            getRow(sheet, 10).createCell(i).setCellValue(str11[i]);
        }

        int rowNum = vo.getDataList().size() + 11;
        BigDecimal moldFee = new BigDecimal(0.0);
        BigDecimal mouldTotalFee = new BigDecimal(0.0);
        BigDecimal textureFee = new BigDecimal(0.0);
        // 设置数据展示行
        for (int i = 0; i < vo.getDataList().size(); i++) {
            DfObject dfObject = vo.getDataList().get(i);
            int rownum = i + 11;
            Row row = sheet.createRow(rownum);
            Cell cell = row.createCell(0);
            cell.setCellValue(i + 1);//序号
            getRow(sheet, rownum).createCell(0).setCellValue("" + (i + 1));
            getRow(sheet, rownum).createCell(1).setCellValue(dfObject.getCodeOfProduct());
            getRow(sheet, rownum).createCell(2).setCellValue(dfObject.getNameOfProduct());

            cellImage1(workbook, sheet, filePath + File.separator + dfObject.getImageOfProduct(), 3, rownum, 3, rownum);


            getRow(sheet, rownum).createCell(4).setCellValue(dfObject.getSurface());
            getRow(sheet, rownum).createCell(5).setCellValue(dfObject.getMaterials());
            String productSize = dfObject.getLengthOfProduct() + "*" + dfObject.getWidthOfProduct() + "*" + dfObject.getHeightOfProduct();
            getRow(sheet, rownum).createCell(6).setCellValue(productSize);
            getRow(sheet, rownum).createCell(7).setCellValue(dfObject.getCavityCount());
            getRow(sheet, rownum).createCell(8).setCellValue(dfObject.getProductWeight());
            getRow(sheet, rownum).createCell(9).setCellValue(dfObject.getPourSystem());
            getRow(sheet, rownum).createCell(10).setCellValue(dfObject.getMaterialOfCavity());
            getRow(sheet, rownum).createCell(11).setCellValue(dfObject.getMaterialOfCore());
            getRow(sheet, rownum).createCell(12).setCellValue(dfObject.getMouldIronMaterial());
            getRow(sheet, rownum).createCell(13).setCellValue(dfObject.getMovingParts());
            getRow(sheet, rownum).createCell(14).setCellValue(dfObject.getHydroCylinder());
            getRow(sheet, rownum).createCell(15).setCellValue(dfObject.getEyeBolt());
            String moldSize = dfObject.getLengthOfMold() + "*" + dfObject.getWidthOfMold() + "*" + dfObject.getHeightOfMold();
            getRow(sheet, rownum).createCell(16).setCellValue(moldSize);

            if (StringUtils.isNotBlank(dfObject.getDeviceId())) {
                TryOutDeviceMod tryOutDeviceMod = tryOutDeviceModService.getById(dfObject.getDeviceId());
                if (null != tryOutDeviceMod) {
                    getRow(sheet, rownum).createCell(17).setCellValue(tryOutDeviceMod.getDeviceName());
                } else {
                    getRow(sheet, rownum).createCell(17).setCellValue("");
                }
            } else {
                getRow(sheet, rownum).createCell(17).setCellValue("");
            }
            getRow(sheet, rownum).createCell(18).setCellValue(formatter2.format(dfObject.getTextureFee()));
            getRow(sheet, rownum).createCell(19).setCellValue(String.valueOf(new BigDecimal(dfObject.getMouldFee()).setScale(4, RoundingMode.HALF_UP)));
            getRow(sheet, rownum).createCell(20).setCellValue(String.valueOf(new BigDecimal(dfObject.getMouldTotalFee()).setScale(4, RoundingMode.HALF_UP)));
            getRow(sheet, rownum).createCell(21).setCellValue(dfObject.getProductionCycle());
            getRow(sheet, rownum).createCell(22).setCellValue(dfObject.getLifetime());
            getRow(sheet, rownum).createCell(23).setCellValue(dfObject.getRemark());
            //计算合计
            moldFee = moldFee.add(new BigDecimal(dfObject.getMouldFee()));

            textureFee = textureFee.add(new BigDecimal(dfObject.getTextureFee()));
            mouldTotalFee = mouldTotalFee.add(new BigDecimal(dfObject.getMouldTotalFee()));
            //子sheet页
            createNewSheet(workbook, i, dfObject, filePath);
        }
        mergedRegionNoStyle(sheet, rowNum, rowNum, 16, 17, "合计:");
        for (int i = 0; i < 24; i++) {
            getCell(getRow(sheet, rowNum), i).setCellStyle(setBorderBottom(workbook));
        }

        // 使用NumberFormat类进行转换
        NumberFormat formatter = new DecimalFormat("#0.0000");
        getRow(sheet, rowNum).createCell(18).setCellValue("￥" + formatter2.format(textureFee));
        getRow(sheet, rowNum).createCell(19).setCellValue("￥" + moldFee.setScale(4, RoundingMode.HALF_UP));
        getRow(sheet, rowNum).createCell(20).setCellValue("￥" + mouldTotalFee.setScale(4, RoundingMode.HALF_UP));

        //设置备注行
        rowNum = rowNum + 1;
        getRow(sheet, rowNum).createCell(0).setCellValue("备注：");
        mergedRegionNoStyle(sheet, rowNum, rowNum, 1, 9, "♦ 此报价含13%的增值税与8%的管理费；含包装运输费用（模具及样件汽运至武汉燎原零部件有限公司），含皮纹费");
        getRow(sheet, rowNum).getCell(0).setCellStyle(customCellStyle(workbook, 16, false, false, "left"));
        getRow(sheet, rowNum).getCell(1).setCellStyle(customCellStyle(workbook, 16, false, false, "left"));

        rowNum = rowNum + 1;
        getRow(sheet, rowNum).createCell(1).setCellValue("♦ 报价包含的试模次数、空运转次数及包含的800PCS合格样件数量以技术任务书要求为准。（试模胶料由客户提供）");
        getRow(sheet, rowNum).getCell(1).setCellStyle(customCellStyle(workbook, 16, false, false, "left"));
        rowNum = rowNum + 1;
        getRow(sheet, rowNum).createCell(1).setCellValue("♦ 标准件采用大连盘起，严格按照<模具外购件清单>购买。");
        getRow(sheet, rowNum).getCell(1).setCellStyle(customCellStyle(workbook, 16, false, false, "left"));
        rowNum = rowNum + 1;
        getRow(sheet, rowNum).createCell(1).setCellValue("♦ 此报价有效期为90天。");
        getRow(sheet, rowNum).getCell(1).setCellStyle(customCellStyle(workbook, 16, false, false, "left"));

        rowNum = rowNum + 2;
        getRow(sheet, rowNum).createCell(2).setCellValue("报价：" + (StringUtils.isBlank(vo.getQuotedPrice()) ? "" : vo.getQuotedPrice()));
        getRow(sheet, rowNum).createCell(11).setCellValue("审核：" + (StringUtils.isBlank(vo.getCheckoutName()) ? "" : vo.getCheckoutName()));
        getRow(sheet, rowNum).createCell(20).setCellValue("批准:" + (StringUtils.isBlank(vo.getApprover()) ? "" : vo.getApprover()));
        //样式
        getRow(sheet, rowNum).getCell(2).setCellStyle(customCellStyle(workbook, 16, false, false, "left"));
        getRow(sheet, rowNum).getCell(11).setCellStyle(customCellStyle(workbook, 16, false, false, ""));
        getRow(sheet, rowNum).getCell(20).setCellStyle(customCellStyle(workbook, 16, false, false, ""));
        rowNum = rowNum + 2;
        mergedRegionNoStyle(sheet, rowNum, rowNum, 16, 21, "成都航天模塑有限公司模具分公司");
        getRow(sheet, rowNum).getCell(16).setCellStyle(customCellStyle(workbook, 16, false, false, ""));
        rowNum = rowNum + 1;
        getRow(sheet, rowNum).createCell(17).setCellValue("日期：");
        mergedRegionNoStyle(sheet, rowNum, rowNum, 18, 20, "");
        getRow(sheet, rowNum).getCell(17).setCellStyle(customCellStyle(workbook, 16, false, false, ""));

        //自定义样式
        getRow(sheet, 0).getCell(0).setCellStyle(customCellStyle(workbook, 26, true, false, ""));
        getRow(sheet, 1).getCell(0).setCellStyle(customCellStyle(workbook, 15, false, false, ""));
        getRow(sheet, 2).getCell(0).setCellStyle(customCellStyle(workbook, 15, false, false, ""));
        getRow(sheet, 4).getCell(0).setCellStyle(customCellStyle(workbook, 15, false, false, ""));
        getRow(sheet, 5).getCell(0).setCellStyle(customCellStyle(workbook, 15, false, false, ""));
        getRow(sheet, 6).getCell(0).setCellStyle(customCellStyle(workbook, 15, false, false, ""));
        getRow(sheet, 7).getCell(0).setCellStyle(customCellStyle(workbook, 15, false, false, ""));
        getRow(sheet, 1).getCell(21).setCellStyle(customCellStyle(workbook, 15, false, false, ""));
        getRow(sheet, 2).getCell(21).setCellStyle(customCellStyle(workbook, 15, false, false, ""));
        getRow(sheet, 3).getCell(21).setCellStyle(customCellStyle(workbook, 15, false, false, ""));
        //模具报价单行
        getRow(sheet, 8).getCell(0).setCellStyle(customCellStyle(workbook, 20, true, false, ""));

        //处理列表展示样式
        for (int j = 10; j <= vo.getDataList().size() + 11; j++) {
            getRow(sheet, j).forEach(c -> {
                if (c.getRowIndex() == 10) {
                    c.setCellStyle(createCellStyle(workbook, 16, true));
                } else if (c.getRowIndex() == vo.getDataList().size() + 11) {
                    c.setCellStyle(createCellStyle(workbook, 16, true));
                } else {
                    c.setCellStyle(createCellStyle(workbook, 14, true));
                }

            });
        }
        for (int j = 1; j <= 7; j++) {
            getCell(getRow(sheet, j), 0).setCellStyle(customCell(workbook, 15));
            getCell(getRow(sheet, j), 1).setCellStyle(customCell(workbook, 15));
            getCell(getRow(sheet, j), 21).setCellStyle(customCell(workbook, 15));
        }

        sheet.getRow(0).setHeight((short) (58.40 * 20));
        sheet.getRow(1).setHeight((short) (24.15 * 20));
        sheet.getRow(2).setHeight((short) (24.15 * 20));
        sheet.getRow(3).setHeight((short) (24.15 * 20));
        sheet.getRow(4).setHeight((short) (24.15 * 20));
        sheet.getRow(5).setHeight((short) (24.15 * 20));
        sheet.getRow(6).setHeight((short) (24.15 * 20));
        sheet.getRow(7).setHeight((short) (24.15 * 20));
        sheet.getRow(8).setHeight((short) (39.70 * 20));
        sheet.getRow(9).setHeight((short) (36.6 * 20));

        for (int i = 10; i < vo.getDataList().size() + 21; i++) {
            getRow(sheet, i).setHeight((short) (53.75 * 20));
        }

        //设置边框
        CellRangeAddress region = new CellRangeAddress(0, vo.getDataList().size() + 20, 0, 23);
        // 设置合并区域的边框样式
        RegionUtil.setBorderTop(BorderStyle.THICK, region, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THICK, region, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THICK, region, sheet);
        RegionUtil.setBorderRight(BorderStyle.THICK, region, sheet);
        // 设置边框颜色
        RegionUtil.setTopBorderColor(IndexedColors.BLUE.getIndex(), region, sheet);
        RegionUtil.setRightBorderColor(IndexedColors.BLUE.getIndex(), region, sheet);
        RegionUtil.setBottomBorderColor(IndexedColors.BLUE.getIndex(), region, sheet);
        RegionUtil.setLeftBorderColor(IndexedColors.BLUE.getIndex(), region, sheet);

        //设置边框
        region = new CellRangeAddress(vo.getDataList().size() + 20, vo.getDataList().size() + 20, 0, 23);
        RegionUtil.setBorderTop(BorderStyle.DOTTED, region, sheet);
        RegionUtil.setTopBorderColor(IndexedColors.BLUE.getIndex(), region, sheet);

        //设置边框
        region = new CellRangeAddress(4, vo.getDataList().size() + 20, 0, 23);
        RegionUtil.setBorderTop(BorderStyle.DOUBLE, region, sheet);
        RegionUtil.setTopBorderColor(IndexedColors.BLACK.getIndex(), region, sheet);

        return PoiUtils.createExcelFile(workbook, fullPath, fileName);
    }

    private CellStyle setBorderBottom(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.NONE);//下边框
        return cellStyle;
    }


    /**
     * 创建新的sheet页
     *
     * @param workbook
     * @param i
     * @param dfObject
     */
    private void createNewSheet(XSSFWorkbook workbook, int i, DfObject dfObject, String filePath) {
        //根据主表数据 创建sheet页【总表一条数据则相应创建一个sheet页】
        Sheet newSheet = workbook.createSheet(String.valueOf(i + 1));
        newSheet.setZoom(85); // 设置默认缩放比例为100%
        DfDetailData detailData = dfObject.getDetailData();

        newSheet.setColumnWidth(0, 11 * 256);
        newSheet.setColumnWidth(1, 7 * 256);
        newSheet.setColumnWidth(2, 13 * 256);
        newSheet.setColumnWidth(3, 9 * 256);
        newSheet.setColumnWidth(4, 10 * 256);
        newSheet.setColumnWidth(5, 7 * 256);
        newSheet.setColumnWidth(6, 20 * 256);
        newSheet.setColumnWidth(7, 10 * 256);
        newSheet.setColumnWidth(8, 9 * 256);
        newSheet.setColumnWidth(9, 2 * 256);
        newSheet.setColumnWidth(10, 8 * 256);
        newSheet.setColumnWidth(11, 2 * 256);
        newSheet.setColumnWidth(12, 5 * 256);
        newSheet.setColumnWidth(13, 0 * 256);
        newSheet.setColumnWidth(14, 0 * 256);

        newSheet.setColumnWidth(17, 27 * 256);
        newSheet.setColumnWidth(18, 33 * 256);
        newSheet.setColumnWidth(19, 30 * 256);

        // 设置标题行
        mergedRegion(newSheet, 0, 4, 0, 4, "成都航天模塑股份有限公司成都模具分公司");
        mergedRegion(newSheet, 0, 2, 5, 17, "模具供应商报价单");
        mergedRegion(newSheet, 0, 2, 18, 18, "联系人/Attention:");
        mergedRegion(newSheet, 0, 2, 19, 19, detailData.getContacts());
        mergedRegion(newSheet, 3, 4, 5, 17, "Mold Quotation Form");
        mergedRegion(newSheet, 3, 4, 18, 18, "电话/TEL:");
        mergedRegion(newSheet, 3, 4, 19, 19, detailData.getPhone());

        //设置分割栏
        mergedRegion(newSheet, 5, 5, 0, 19, "");

        List<TempVo> list = Lists.newArrayList();
        list.add(new TempVo("模具名称/Mold Specification", "型腔数量/Cav.", "产品零件号/Part No.", "首次试模时间/T1 Time"));
        list.add(new TempVo(detailData.getMoldName(), detailData.getCavityCount(), detailData.getCodeOfProduct(), detailData.getT0Time()));
        list.add(new TempVo("产品大小/Part Size (mm)", "产品重量/Part Weight(g)", "数据文件名/Drawing No.", "最终交付时间/Lead Time"));
        String productSize = detailData.getLengthOfProduct() + "*" + detailData.getWidthOfProduct() + "*" + detailData.getHeightOfProduct();
        list.add(new TempVo(productSize, String.valueOf(detailData.getWeightOfProduct()), detailData.getDrawingNo(), detailData.getLeadTime()));
        list.add(new TempVo("外形尺寸/Mold Size (mm)", "模具重量/Mold Weight(Kg)", "模具寿命/Longevity", "设备吨位/Press(Ton)"));
        String moldSize = detailData.getLengthOfShape() + "*" + detailData.getWidthOfShape() + "*" + detailData.getHeightOfShape();
        list.add(new TempVo(moldSize, String.valueOf(detailData.getMoldWeight()), detailData.getLongevity(), detailData.getPressTon()));

        int rowNum = 5;
        for (int i1 = 0; i1 < list.size(); i1++) {
            rowNum = rowNum + 1;
            TempVo tempVo = list.get(i1);
            mergedRegion(newSheet, rowNum, rowNum, 0, 4, tempVo.getParam1());
            mergedRegion(newSheet, rowNum, rowNum, 5, 6, tempVo.getParam2());
            mergedRegion(newSheet, rowNum, rowNum, 17, 18, tempVo.getParam3());
            getRow(newSheet, rowNum).createCell(19).setCellValue(tempVo.getParam4());
            if (rowNum == 6) {
                cellImage1(workbook, newSheet, filePath + File.separator + dfObject.getImageOfProduct(), 7, 6, 16, 11);
            }
        }
        //数据分割线---以下加工材料费
        mergedRegion(newSheet, 12, 12, 0, 19, "");

        rowNum = 13;
        mergedRegion(newSheet, 13, 22, 0, 1, "加工材料费/  Machining Material Cost");
        mergedRegion(newSheet, rowNum, rowNum, 2, 5, "材料名称/Material");
        mergedRegion(newSheet, rowNum, rowNum, 6, 7, "材料牌号/Specification");
        mergedRegion(newSheet, rowNum, rowNum, 8, 14, "尺寸/Size(mm×mm×mm)");
        mergedRegion(newSheet, rowNum, rowNum, 15, 16, "重量/Weight(Kg)");
        getRow(newSheet, rowNum).createCell(17).setCellValue("单价(元)/Unit Price(RMB)");
        getRow(newSheet, rowNum).createCell(18).setCellValue("金额(元)/Price(RMB)");
        getRow(newSheet, rowNum).createCell(19).setCellValue("小计(元)/Subtotal(RMB)");

        DecimalFormat decimalFormat2 = new DecimalFormat("0.00");
        DecimalFormat decimalFormat3 = new DecimalFormat("0.000"); // 指定格式化模式，保留三位小数
        DfMachiningMaterialCost machiningMaterialCost = detailData.getMachiningMaterialCost();
        if (null != machiningMaterialCost && machiningMaterialCost.getDataList().size() > 0) {
            List<ItemMach> dataList = machiningMaterialCost.getDataList();
            int size = dataList.size();
            for (int i1 = 0; i1 < size; i1++) {
                rowNum = rowNum + 1;
                ItemMach itemMach = machiningMaterialCost.getDataList().get(i1);
                mergedRegion(newSheet, rowNum, rowNum, 2, 5, itemMach.getItemName());
                mergedRegion(newSheet, rowNum, rowNum, 6, 7, itemMach.getSpecification());
                getCell(getRow(newSheet, rowNum), 8).setCellValue(String.valueOf(itemMach.getLength()));
                getCell(getRow(newSheet, rowNum), 9).setCellValue("X");
                getCell(getRow(newSheet, rowNum), 10).setCellValue(String.valueOf(itemMach.getWidth()));
                getCell(getRow(newSheet, rowNum), 11).setCellValue("X");
                getCell(getRow(newSheet, rowNum), 12).setCellValue(itemMach.getHeight());
                mergedRegion(newSheet, rowNum, rowNum, 15, 16, decimalFormat3.format(itemMach.getWeight()));
                getCell(getRow(newSheet, rowNum), 17).setCellValue(decimalFormat2.format(itemMach.getPrice()));
                getCell(getRow(newSheet, rowNum), 18).setCellValue(decimalFormat2.format(itemMach.getTotalPrice()));
                int lastRow = rowNum + size - 1;
                if (i1 == 0) {
                    mergedRegion(newSheet, rowNum, lastRow, 19, 19, decimalFormat2.format(machiningMaterialCost.getSubTotal()));
                }

            }

        }
        //数据分割线  加背景颜色---以下热处理
        mergedRegion(newSheet, 23, 23, 0, 19, "");

        rowNum = 24;
        mergedRegion(newSheet, 24, 27, 0, 1, "热处理/Heat Treatment");
        mergedRegion(newSheet, rowNum, rowNum, 2, 5, "热处理名称/Item");
        mergedRegion(newSheet, rowNum, rowNum, 6, 7, "重量/Weight(Kg)");
        mergedRegion(newSheet, rowNum, rowNum, 8, 16, "单价(元)/Unit Price(RMB)");
        mergedRegion(newSheet, rowNum, rowNum, 17, 18, "金额(元)/Price(RMB)");
        getRow(newSheet, rowNum).createCell(19).setCellValue("小计(元)/Subtotal(RMB)");

        DfHeatTreatmentCost heatTreatmentCost = detailData.getHeatTreatmentCost();
        if (null != heatTreatmentCost && heatTreatmentCost.getDataList().size() > 0) {
            List<ItemHeat> dataList = heatTreatmentCost.getDataList();
            int size = dataList.size();
            for (int i1 = 0; i1 < size; i1++) {
                rowNum = rowNum + 1;
                ItemHeat itemHeat = dataList.get(i1);
                int lastRow = rowNum + size - 1;
                mergedRegion(newSheet, rowNum, rowNum, 2, 5, itemHeat.getItemName());
                mergedRegion(newSheet, rowNum, rowNum, 6, 7, decimalFormat3.format(itemHeat.getWeight()));
                mergedRegion(newSheet, rowNum, rowNum, 8, 16, decimalFormat2.format(itemHeat.getPrice()));
                mergedRegion(newSheet, rowNum, rowNum, 17, 18, decimalFormat2.format(itemHeat.getTotalPrice()));
                if (i1 == 0) {
                    mergedRegion(newSheet, rowNum, lastRow, 19, 19, decimalFormat2.format(heatTreatmentCost.getSubTotal()));
                }
            }
        }

        //数据分割线  加背景颜色    ---以下装配材料费
        mergedRegion(newSheet, 28, 28, 0, 19, "");


        rowNum = 29;
        mergedRegion(newSheet, 29, 37, 0, 1, "装配材料费/Assemly Material Cost");
        mergedRegion(newSheet, rowNum, rowNum, 2, 5, "装配件/Item");
        mergedRegion(newSheet, rowNum, rowNum, 6, 7, "规格型号/Specification");
        mergedRegion(newSheet, rowNum, rowNum, 8, 12, "品牌/Supplier");
        mergedRegion(newSheet, rowNum, rowNum, 13, 16, "数量/Number");
        getRow(newSheet, rowNum).createCell(17).setCellValue("单价(元)/Unit Price(RMB)");
        getRow(newSheet, rowNum).createCell(18).setCellValue("金额(元)/Price(RMB)");
        getRow(newSheet, rowNum).createCell(19).setCellValue("小计(元)/Subtotal(RMB)");

        DfAssemlyMaterialCost assemlyMaterialCost = detailData.getAssemlyMaterialCost();
        if (null != assemlyMaterialCost && assemlyMaterialCost.getDataList().size() > 0) {
            List<ItemAssemly> dataList = assemlyMaterialCost.getDataList();
            int size = dataList.size();
            for (int i1 = 0; i1 < size; i1++) {
                rowNum = rowNum + 1;
                ItemAssemly itemAssemly = dataList.get(i1);
                int lastRow = rowNum + size - 1;
                mergedRegion(newSheet, rowNum, rowNum, 2, 5, itemAssemly.getItemName());
                mergedRegion(newSheet, rowNum, rowNum, 6, 7, itemAssemly.getSpecification());
                mergedRegion(newSheet, rowNum, rowNum, 8, 12, itemAssemly.getSupplier());
                mergedRegion(newSheet, rowNum, rowNum, 13, 16, BigdecimalUtil.double2IntegerString(itemAssemly.getCount()));
                getRow(newSheet, rowNum).createCell(17).setCellValue(decimalFormat2.format(itemAssemly.getPrice()));
                getRow(newSheet, rowNum).createCell(18).setCellValue(decimalFormat2.format(itemAssemly.getTotalPrice()));
                if (i1 == 0) {
                    mergedRegion(newSheet, rowNum, lastRow, 19, 19, decimalFormat2.format(assemlyMaterialCost.getSubTotal()));
                }
            }
        }
        //数据分割线  加背景颜色    ---以下设计费
        mergedRegion(newSheet, 38, 38, 0, 19, "");


        rowNum = 39;
        mergedRegion(newSheet, 39, 42, 0, 1, "设计费/Design Cost");
        mergedRegion(newSheet, rowNum, rowNum, 2, 5, "名称/Item");
        mergedRegion(newSheet, rowNum, rowNum, 6, 7, "工时(小时)/Hour");
        mergedRegion(newSheet, rowNum, rowNum, 8, 16, "单价(元)/Unit Price(RMB)");
        mergedRegion(newSheet, rowNum, rowNum, 17, 18, "金额(元)/Price(RMB)");
        getRow(newSheet, rowNum).createCell(19).setCellValue("小计(元)/Subtotal(RMB)");

        DfDesignCost designCost = detailData.getDesignCost();
        if (null != designCost && designCost.getDataList().size() > 0) {
            List<ItemDesign> dataList = designCost.getDataList();
            int size = dataList.size();
            for (int i1 = 0; i1 < size; i1++) {
                rowNum = rowNum + 1;
                ItemDesign itemDesign = dataList.get(i1);
                int lastRow = rowNum + size - 1;
                mergedRegion(newSheet, rowNum, rowNum, 2, 5, itemDesign.getItemName());
                mergedRegion(newSheet, rowNum, rowNum, 6, 7, String.valueOf(itemDesign.getHour()));
                mergedRegion(newSheet, rowNum, rowNum, 8, 16, decimalFormat2.format(itemDesign.getPrice()));
                mergedRegion(newSheet, rowNum, rowNum, 17, 18, decimalFormat2.format(itemDesign.getTotalPrice()));
                if (i1 == 0) {
                    mergedRegion(newSheet, rowNum, lastRow, 19, 19, decimalFormat2.format(designCost.getSubTotal()));
                }
            }
        }

        //数据分割线  加背景颜色    ---以下加工费
        mergedRegion(newSheet, 43, 43, 0, 19, "");
        rowNum = 44;
        mergedRegion(newSheet, 44, 55, 0, 1, "加工费/   Manufacturing Cost");
        mergedRegion(newSheet, rowNum, rowNum, 2, 5, "名称/Item");
        mergedRegion(newSheet, rowNum, rowNum, 6, 7, "工时(小时)/Hour");
        mergedRegion(newSheet, rowNum, rowNum, 8, 16, "单价(元)/Unit Price(RMB)");
        mergedRegion(newSheet, rowNum, rowNum, 17, 18, "金额(元)/Price(RMB)");
        getRow(newSheet, rowNum).createCell(19).setCellValue("小计(元)/Subtotal(RMB)");

        DfManufacturingCost manufacturingCost = detailData.getManufacturingCost();
        if (null != manufacturingCost && manufacturingCost.getDataList().size() > 0) {
            List<ItemManufacturing> dataList = manufacturingCost.getDataList();
            int size = dataList.size();
            for (int i1 = 0; i1 < size; i1++) {
                rowNum = rowNum + 1;
                ItemManufacturing itemManufacturing = dataList.get(i1);
                int lastRow = rowNum + size - 1;
                mergedRegion(newSheet, rowNum, rowNum, 2, 5, itemManufacturing.getItemName());
                mergedRegion(newSheet, rowNum, rowNum, 6, 7, String.valueOf(itemManufacturing.getHour()));
                mergedRegion(newSheet, rowNum, rowNum, 8, 16, decimalFormat2.format(itemManufacturing.getPrice()));
                mergedRegion(newSheet, rowNum, rowNum, 17, 18, decimalFormat2.format(itemManufacturing.getTotalPrice()));
                if (i1 == 0) {
                    mergedRegion(newSheet, rowNum, lastRow, 19, 19, decimalFormat2.format(manufacturingCost.getSubTotal()));
                }
            }
        }

        //数据分割线  加背景颜色    ---以下三坐标测量费CMM
        mergedRegion(newSheet, 56, 56, 0, 19, "");

        rowNum = 57;
        mergedRegion(newSheet, 57, 59, 0, 1, "三坐标测量费CMM Measure");
        mergedRegion(newSheet, rowNum, rowNum, 2, 5, "名称/Item");
        mergedRegion(newSheet, rowNum, rowNum, 6, 7, "工时(小时)/Hour");
        mergedRegion(newSheet, rowNum, rowNum, 8, 16, "单价(元)/Unit Price(RMB)");
        mergedRegion(newSheet, rowNum, rowNum, 17, 18, "金额(元)/Price(RMB)");
        getRow(newSheet, rowNum).createCell(19).setCellValue("小计(元)/Subtotal(RMB)");

        DfCMMMeasure cmmMeasure = detailData.getCMMMeasure();
        if (null != cmmMeasure && cmmMeasure.getDataList().size() > 0) {
            List<ItemCMMM> dataList = cmmMeasure.getDataList();
            int size = dataList.size();
            for (int i1 = 0; i1 < size; i1++) {
                rowNum = rowNum + 1;
                ItemCMMM itemCMMM = dataList.get(i1);
                int lastRow = rowNum + size - 1;
                mergedRegion(newSheet, rowNum, rowNum, 2, 5, itemCMMM.getItemName());
                mergedRegion(newSheet, rowNum, rowNum, 6, 7, String.valueOf(itemCMMM.getHour()));
                mergedRegion(newSheet, rowNum, rowNum, 8, 16, decimalFormat2.format(itemCMMM.getPrice()));
                mergedRegion(newSheet, rowNum, rowNum, 17, 18, decimalFormat2.format(itemCMMM.getTotalPrice()));
                if (i1 == 0) {
                    mergedRegion(newSheet, rowNum, lastRow, 19, 19, decimalFormat2.format(cmmMeasure.getSubTotal()));
                }
            }
        }

        //数据分割线  加背景颜色    ---以下其它费用/Other Fee
        mergedRegion(newSheet, 60, 60, 0, 19, "");

        rowNum = 61;
        mergedRegion(newSheet, 61, 66, 0, 1, "其它费用/Other Fee");
        mergedRegion(newSheet, rowNum, rowNum, 2, 5, "费用名称/Item");
        mergedRegion(newSheet, rowNum, rowNum, 6, 16, "费用计算说明/Descripition");
        mergedRegion(newSheet, rowNum, rowNum, 17, 18, "金额(元)/Price(RMB)");
        getRow(newSheet, rowNum).createCell(19).setCellValue("小计(元)/Subtotal(RMB)");

        DfOtherFee otherFee = detailData.getOtherFee();
        if (null != otherFee && otherFee.getDataList().size() > 0) {
            List<ItemOther> dataList = otherFee.getDataList();
            int size = dataList.size();
            for (int i1 = 0; i1 < size; i1++) {
                rowNum = rowNum + 1;
                ItemOther itemOther = dataList.get(i1);
                int lastRow = rowNum + size - 1;
                mergedRegion(newSheet, rowNum, rowNum, 2, 5, itemOther.getFeeName());
                mergedRegion(newSheet, rowNum, rowNum, 6, 16, String.valueOf(itemOther.getFeeDesc()));
                mergedRegion(newSheet, rowNum, rowNum, 17, 18, decimalFormat2.format(itemOther.getTotalPrice()));
                if (i1 == 0) {
                    mergedRegion(newSheet, rowNum, lastRow, 19, 19, decimalFormat2.format(otherFee.getSubTotal()));
                }
            }
        }

        //数据分割线  加背景颜色
        mergedRegion(newSheet, 67, 67, 0, 19, "");

        //设置页尾
        mergedRegion(newSheet, 68, 70, 0, 5, "皮纹价格（元）\nRMB");
        mergedRegion(newSheet, 68, 70, 6, 19, "￥" + BigdecimalUtil.doubleTo2DecimalString(detailData.getSkinGrainFee()));
        mergedRegion(newSheet, 72, 74, 0, 5, "模具总价(元)\n(RMB)");
        mergedRegion(newSheet, 72, 74, 6, 19, "￥" + BigdecimalUtil.doubleTo2DecimalString(detailData.getMouldFee()));

//        BigDecimal totalPrice = new BigDecimal(0.0);
//        if (null != machiningMaterialCost) {
//            totalPrice = totalPrice.add(new BigDecimal(machiningMaterialCost.getSubTotal()));
//        }
//        if (null != heatTreatmentCost) {
//            totalPrice = totalPrice.add(new BigDecimal(heatTreatmentCost.getSubTotal()));
//        }
//        if (null != assemlyMaterialCost) {
//            totalPrice = totalPrice.add(new BigDecimal(assemlyMaterialCost.getSubTotal()));
//        }
//        if (null != designCost) {
//            totalPrice = totalPrice.add(new BigDecimal(designCost.getSubTotal()));
//        }
//        if (null != manufacturingCost) {
//            totalPrice = totalPrice.add(new BigDecimal(manufacturingCost.getSubTotal()));
//        }
//        if (null != cmmMeasure) {
//            totalPrice = totalPrice.add(new BigDecimal(cmmMeasure.getSubTotal()));
//        }
//        if (null != otherFee) {
//            totalPrice = totalPrice.add(new BigDecimal(otherFee.getSubTotal()));
//        }
        mergedRegion(newSheet, 76, 78, 0, 5, "总价(元)\n(RMB)");
        mergedRegion(newSheet, 76, 78, 6, 19, "￥" + BigdecimalUtil.doubleTo2DecimalString(detailData.getTotalPrice()));
//        mergedRegion(newSheet, 76, 78, 6, 19, "￥" + BigdecimalUtil.bigDecimalTo2DecimalString(totalPrice));

        //设置页尾
        getRow(newSheet, 81).createCell(1).setCellValue("供应商/Supplier：");
        mergedRegionNoStyle(newSheet, 81, 81, 4, 12, detailData.getSupplier());
        getRow(newSheet, 81).createCell(15).setCellValue("签字/Signature:");
        getRow(newSheet, 81).createCell(19).setCellValue(detailData.getSignature());

        getRow(newSheet, 83).createCell(1).setCellValue("盖章/Stamp:");
        getRow(newSheet, 83).createCell(5).setCellValue(detailData.getStamp());
        getRow(newSheet, 83).createCell(15).setCellValue("日期/Date:");
        getRow(newSheet, 83).createCell(17).setCellValue(detailData.getDate());


        List<Integer> colorList = new ArrayList<>();
        colorList.add(5);
        colorList.add(12);
        colorList.add(23);
        colorList.add(28);
        colorList.add(38);
        colorList.add(43);
        colorList.add(56);
        colorList.add(60);
        colorList.add(67);

        // 设置整个表格的样式
        for (int j = 0; j <= 78; j++) {
            getRow(newSheet, j).forEach(c -> {
                c.setCellStyle(createCellStyle(workbook, 12, false));
            });
        }

        colorList.forEach(index -> {
            getRow(newSheet, index).getCell(0).setCellStyle(specialCellStyle(workbook, true, 18));
        });

        List<Integer> fontList = new ArrayList<>();
        fontList.add(0);
        fontList.add(3);
        fontList.forEach(index -> {
            getRow(newSheet, index).getCell(0).setCellStyle(specialCellStyle(workbook, false, 18));
            getRow(newSheet, index).getCell(5).setCellStyle(specialCellStyle(workbook, false, 18));
        });

        List<Integer> fontList1 = new ArrayList<>();
        fontList1.add(68);
        fontList1.add(72);
        fontList1.add(76);
        fontList1.forEach(index -> {
            getRow(newSheet, index).getCell(0).setCellStyle(specialCellStyle(workbook, false, 12));
            getRow(newSheet, index).getCell(6).setCellStyle(specialCellStyle(workbook, false, 16));
        });

        List<Integer> fontList2 = new ArrayList<>();
        fontList2.add(81);
        fontList2.add(83);
        fontList2.forEach(index -> {
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillPattern(FillPatternType.NO_FILL);
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setFillForegroundColor((short) 9);
            XSSFFont font = workbook.createFont();
            //设置字体名称
            font.setFontName("宋体");
            font.setBold(true);
            font.setFontHeightInPoints((short) 16);//设置字号

            //设置字体颜色
            font.setColor(IndexedColors.BLUE.index);
            cellStyle.setFont(font);
            getRow(newSheet, index).getCell(1).setCellStyle(cellStyle);
            if(index == 81){
                getRow(newSheet, index).getCell(4).setCellStyle(cellStyle);

            }
            getCell(getRow(newSheet, index),15).setCellStyle(cellStyle);
            getCell(getRow(newSheet, index),17).setCellStyle(cellStyle);
        });

        List<Integer> wrapTextList = new ArrayList<>();
        wrapTextList.add(13);
        wrapTextList.add(24);
        wrapTextList.add(29);
        wrapTextList.add(39);
        wrapTextList.add(44);
        wrapTextList.add(57);
        wrapTextList.add(61);
        wrapTextList.forEach(index -> {
            getRow(newSheet, index).getCell(0).setCellStyle(createCellStyle(workbook, 12, true));
        });
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderLeft(BorderStyle.THICK);
        getCell(getRow(newSheet, 80), 20).setCellStyle(cellStyle);
        getCell(getRow(newSheet, 81), 20).setCellStyle(cellStyle);
        getCell(getRow(newSheet, 82), 20).setCellStyle(cellStyle);
        getCell(getRow(newSheet, 83), 20).setCellStyle(cellStyle);
        getCell(getRow(newSheet, 84), 20).setCellStyle(cellStyle);
        for (int j = 0; j < 20; j++) {
            cellStyle = workbook.createCellStyle();
            cellStyle.setBorderTop(BorderStyle.THICK);
            getCell(getRow(newSheet, 80), j).setCellStyle(cellStyle);
        }

        for (int j = 0; j < 20; j++) {
            cellStyle = workbook.createCellStyle();
            cellStyle.setBorderBottom(BorderStyle.THICK);
            getCell(getRow(newSheet, 84), j).setCellStyle(cellStyle);
        }

        getRow(newSheet, 68).setHeight((short) (22.0 * 20));
        getRow(newSheet, 72).setHeight((short) (22.0 * 20));
        getRow(newSheet, 76).setHeight((short) (22.0 * 20));

        //设置外边框  上部分-------------------------------
        RegionUtil.setBorderBottom(BorderStyle.NONE, new CellRangeAddress(2, 2, 5, 17), newSheet);
        RegionUtil.setBorderTop(BorderStyle.NONE, new CellRangeAddress(3, 3, 5, 17), newSheet);

        RegionUtil.setBorderRight(BorderStyle.THICK, new CellRangeAddress(0, 4, 0, 4), newSheet);
        RegionUtil.setBorderRight(BorderStyle.THICK, new CellRangeAddress(0, 4, 17, 17), newSheet);
        RegionUtil.setBorderRight(BorderStyle.THICK, new CellRangeAddress(0, 4, 18, 18), newSheet);
        RegionUtil.setBorderRight(BorderStyle.THICK, new CellRangeAddress(0, 4, 19, 19), newSheet);

        RegionUtil.setBorderBottom(BorderStyle.THICK, new CellRangeAddress(4, 4, 0, 19), newSheet);
        RegionUtil.setBorderTop(BorderStyle.THICK, new CellRangeAddress(6, 6, 0, 6), newSheet);
        RegionUtil.setBorderTop(BorderStyle.THICK, new CellRangeAddress(6, 6, 17, 19), newSheet);
        for (int j = 6; j <=11 ; j++) {
            RegionUtil.setBorderRight(BorderStyle.THICK, new CellRangeAddress(j, j, 4, 4), newSheet);
            RegionUtil.setBorderRight(BorderStyle.THICK, new CellRangeAddress(j, j, 18, 18), newSheet);
            RegionUtil.setBorderRight(BorderStyle.THICK, new CellRangeAddress(j, j, 19, 19), newSheet);
        }
        //设置外边框  列表部分---------------------------
        int heightSize = 13;
        List<Integer> borStyleList = Lists.newArrayList();
        borStyleList.add(heightSize);
        heightSize= heightSize + machiningMaterialCost.getDataList().size()+2;//加工材料费
        borStyleList.add(heightSize);
        heightSize= heightSize + heatTreatmentCost.getDataList().size()+2;//热处理
        borStyleList.add(heightSize);
        heightSize= heightSize + assemlyMaterialCost.getDataList().size()+2;//装配材料费
        borStyleList.add(heightSize);
        heightSize= heightSize + designCost.getDataList().size()+2;//设计费
        borStyleList.add(heightSize);
        heightSize= heightSize + manufacturingCost.getDataList().size()+2;//加工费
        borStyleList.add(heightSize);
        heightSize= heightSize + cmmMeasure.getDataList().size()+2;//三坐标测量费CMM
        borStyleList.add(heightSize);
        heightSize= heightSize + otherFee.getDataList().size()+2;//其它费用/Other Fee
        borStyleList.add(heightSize);
        //处理标题行的边框样式
        for (int i1 = 0; i1 < borStyleList.size(); i1++) {
            Integer num = borStyleList.get(i1);
            //处理当前标题行 上一行的边框 【这里指空白行】
            RegionUtil.setBorderBottom(BorderStyle.THICK, new CellRangeAddress(num-2, num-2, 0, 19), newSheet);
            RegionUtil.setBorderTop(BorderStyle.THICK, new CellRangeAddress(num, num, 0, 19), newSheet);
            RegionUtil.setBorderRight(BorderStyle.THICK, new CellRangeAddress(num, num, 1, 1), newSheet);
            RegionUtil.setBorderRight(BorderStyle.THICK, new CellRangeAddress(num, num, 18, 18), newSheet);
            RegionUtil.setBorderRight(BorderStyle.THICK, new CellRangeAddress(num, num, 19, 19), newSheet);
            RegionUtil.setBorderBottom(BorderStyle.THICK, new CellRangeAddress(num, num, 0, 19), newSheet);
            RegionUtil.setBorderBottom(BorderStyle.THICK, new CellRangeAddress(num, num, 0, 19), newSheet);
        }
        //去除区间值[排除上面已添加样式的行]
        int min = Collections.min(borStyleList);
        int max = Collections.max(borStyleList);
        List<Integer> result = IntStream.range(min, max + 1).filter(num -> !borStyleList.contains(num)).boxed()
                .collect(Collectors.toList());
        for (Integer number : result) {
            RegionUtil.setBorderRight(BorderStyle.THICK, new CellRangeAddress(number, number, 1, 1), newSheet);
            RegionUtil.setBorderRight(BorderStyle.THICK, new CellRangeAddress(number, number, 18, 18), newSheet);
            RegionUtil.setBorderRight(BorderStyle.THICK, new CellRangeAddress(number, number, 19, 19), newSheet);
        }

        //总计部分----------------------------------
        //处理批文价格、模具总价、总价 边框----当前行的右边框
        Integer max1 = Collections.max(result);
        int[] indices = {1,2,3, 5, 6, 7, 9,10, 11};
        for (int number : indices) {
            RegionUtil.setBorderRight(BorderStyle.THICK, new CellRangeAddress(max1 + number, max1 + number, 5, 5), newSheet);
            RegionUtil.setBorderRight(BorderStyle.THICK, new CellRangeAddress(max1 + number, max1 + number, 18, 18), newSheet);
            RegionUtil.setBorderRight(BorderStyle.THICK, new CellRangeAddress(max1 + number, max1 + number, 19, 19), newSheet);
        }

        RegionUtil.setBorderTop(BorderStyle.THICK, new CellRangeAddress(71,71, 0, 19), newSheet);
        RegionUtil.setBorderTop(BorderStyle.THICK, new CellRangeAddress(72,72, 0, 19), newSheet);
        RegionUtil.setBorderTop(BorderStyle.THICK, new CellRangeAddress(75,75, 0, 19), newSheet);
        RegionUtil.setBorderTop(BorderStyle.THICK, new CellRangeAddress(76,76, 0, 19), newSheet);
        RegionUtil.setBorderTop(BorderStyle.THICK, new CellRangeAddress(79,79, 0, 19), newSheet);
    }


    /**
     * 合并单元格
     *
     * @param sheet
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     * @param cellValue
     */
    private void mergedRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, String cellValue) {
        // 合并单元格
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(region);
        //设置样式
        regionStyle(sheet, region);
        //赋值
        getRow(sheet, firstRow).createCell(firstCol).setCellValue(cellValue);
    }

    /**
     * 合并单元格 不加样式
     *
     * @param sheet
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     * @param cellValue
     */
    private void mergedRegionNoStyle(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, String cellValue) {
        // 合并单元格
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(region);
        //赋值
        getRow(sheet, firstRow).createCell(firstCol).setCellValue(cellValue);
    }

    /**
     * 获取行
     *
     * @param sheet
     * @param firstRow
     * @return
     */
    private Row getRow(Sheet sheet, int firstRow) {
        return sheet.getRow(firstRow) == null ? sheet.createRow(firstRow) : sheet.getRow(firstRow);
    }

    private Cell getCell(Row row, int firstCell) {
        return row.getCell(firstCell) == null ? row.createCell(firstCell) : row.getCell(firstCell);
    }

    /**
     * 设置区域样式
     *
     * @param sheet
     * @param cellAddresses
     */
    private void regionStyle(Sheet sheet, CellRangeAddress cellAddresses) {
        // 设置合并区域的边框样式
        RegionUtil.setBorderTop(BorderStyle.THICK, cellAddresses, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THICK, cellAddresses, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THICK, cellAddresses, sheet);
        RegionUtil.setBorderRight(BorderStyle.THICK, cellAddresses, sheet);
    }

    /**
     * 设置单元格样式
     */
    private XSSFCellStyle createCellStyle(XSSFWorkbook workbook, int fontSize, boolean isWrapText) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //加边框
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        cellStyle.setFillForegroundColor((short) 9);
        cellStyle.setWrapText(isWrapText);
        XSSFFont font = workbook.createFont();
        //设置字体名称
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) fontSize);//设置字号
        //设置字体颜色
        font.setColor(IndexedColors.BLACK1.index);
        cellStyle.setFont(font);

        return cellStyle;
    }


    /**
     * 特殊样式
     *
     * @param workbook
     * @return
     */
    private XSSFCellStyle customCellStyle(XSSFWorkbook workbook, int fontSize, boolean isBold, boolean isWrapText, String alignment) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(FillPatternType.NO_FILL);
        if ("left".equals(alignment)) {
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
        } else {
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
        }
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setWrapText(isWrapText);
        XSSFFont font = workbook.createFont();
        //设置字体名称
        font.setFontName("宋体");
        font.setBold(isBold);
        font.setFontHeightInPoints((short) fontSize);//设置字号
        cellStyle.setFont(font);

        return cellStyle;
    }

    private XSSFCellStyle customCell(XSSFWorkbook workbook, int fontSize) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(FillPatternType.NO_FILL);
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setWrapText(false);
        XSSFFont font = workbook.createFont();
        //设置字体名称
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) fontSize);//设置字号
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 特殊样式
     *
     * @param workbook
     * @return
     */
    private XSSFCellStyle specialCellStyle(XSSFWorkbook workbook, boolean isForeground, int fontSize) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setWrapText(true);
        //加边框
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        if (isForeground) {
            //自定义颜色对象
            cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(253, 233, 217)));
        } else {
            cellStyle.setFillForegroundColor((short) 9);
        }

        XSSFFont font = workbook.createFont();
        //设置字体名称
        font.setFontName("宋体");
        font.setBold(true);
        font.setFontHeightInPoints((short) fontSize);//设置字号
        //设置字体颜色
        font.setColor(IndexedColors.BLUE.index);
        cellStyle.setFont(font);

        return cellStyle;
    }

    public static void cellImage1(Workbook wb, Sheet sheet, String imageUrl, int firstCol, int firstRow, int lastCol, int lastRow) {
        try {
            // 读取图片
            InputStream is = new FileInputStream(imageUrl);
            byte[] bytes = IOUtils.toByteArray(is);
            int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
            is.close();

            CreationHelper helper = wb.getCreationHelper();

            // 设置行高和列宽
            // 由于POI的行高是以1/20个点为单位，故实际行高为53.75 * 20
            sheet.getRow(firstRow).setHeight((short) (53.75 * 20));
            // 列宽是以1/256个字符宽度为单位，故实际列宽为26.39 * 256
            sheet.setColumnWidth(firstCol, (int) (26 * 256));

            // 创建图像并插入单元格
            Drawing drawing = sheet.createDrawingPatriarch();
            ClientAnchor anchor = helper.createClientAnchor();
            // 将图片插入到指定的单元格
            anchor.setCol1(firstCol);
            anchor.setRow1(firstRow);
            anchor.setCol2(lastCol); // 图片占据一个单元格的空间
            anchor.setRow2(lastRow);

            int emuPerPixel = Units.EMU_PER_PIXEL;
            int dx = 3 * emuPerPixel; // 向右移动10个像素
            int dy = 10 * emuPerPixel; // 向下移动10个像素

            anchor.setDx1(dx);
            anchor.setDy1(dy);
            anchor.setDx2(dx);
            anchor.setDy2(dy);
            Picture pict = drawing.createPicture(anchor, pictureIdx);

            // 获取图像的原始大小
            double imageWidth = pict.getImageDimension().getWidth();
            double imageHeight = pict.getImageDimension().getHeight();

            // 获取单元格的宽度和高度
            double cellWidth = sheet.getColumnWidthInPixels(firstCol);
            double cellHeight = sheet.getRow(firstRow).getHeightInPoints() / 72 * 96;

            // 计算图像的新大小以适应单元格
            double ratio = Math.min(cellWidth / imageWidth, cellHeight / imageHeight) * 0.9;
            int newWidth = (int) (imageWidth * ratio);
            int newHeight = (int) (imageHeight * ratio);

            anchor.setDx1(Units.toEMU(3));
            anchor.setDy1(Units.toEMU(0));
            anchor.setDx2(Units.toEMU(newWidth + 3)); // 将图像向右移动3个像素
            anchor.setDy2(Units.toEMU(newHeight));

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
